package com.hsnn.medstgmini.pro.project.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.LobHelper;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.BidContractDao;
import com.hsnn.medstgmini.pro.project.model.BidContract;
import com.hsnn.medstgmini.pro.project.model.BidContractLog;
import com.hsnn.medstgmini.pro.project.model.BidContractParty;
import com.hsnn.medstgmini.pro.project.model.BidContractPartyLog;
import com.hsnn.medstgmini.pro.project.model.BidContractProd;
import com.hsnn.medstgmini.pro.project.model.BidContractProdLog;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class BidContractDaoImpl implements BidContractDao{
	
	@Autowired
	private HibernateSqlUtil hibernateUtil;
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	/**
	 * @category 获取合同列表
	 * @author zhouqi
	 * @date 2015年9月9日15:10:35
	 * @param page
	 * @return Pagination
	 */
	public Pagination getContractListPage(Pagination page){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                                         ");
		sql.append("A.contract_title AS contractTitle,                             ");
		sql.append("A.contract_id AS contractId,                                   ");
		sql.append("A.contract_code AS contractCode,                               ");
		sql.append("date_format(A.audit_datetime,'%Y-%m-%d %H:%i:%S') AS auditDatetime, ");
		sql.append("A.audit_status AS auditStatus,                                 ");
		sql.append("A.`status` AS status,                                          ");
		sql.append("date_format(B.signup_datetime,'%Y-%m-%d %H:%i:%S')AS signupDatetime, ");
		sql.append("E.`name` AS projName,                                          ");
		sql.append("(SELECT std_compinfo.comp_name                                 ");
		sql.append("FROM bid_contract                                              ");
		sql.append("LEFT JOIN bid_contract_party                                   ");
		sql.append("ON bid_contract.contract_id = bid_contract_party.contract_id   ");
		sql.append("LEFT JOIN contract_model                                       ");
		sql.append("ON bid_contract.contract_model_id = contract_model.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo                                         ");
		sql.append("ON bid_contract_party.contract_party = std_compinfo.comp_id    ");
		sql.append("WHERE bid_contract.contract_id = A.contract_id                 ");
		sql.append("AND bid_contract_party.is_party_a = 1 ) AS contractPartyA,     ");
		sql.append("(SELECT std_compinfo.comp_name                                 ");
		sql.append("FROM bid_contract                                              ");
		sql.append("LEFT JOIN bid_contract_party                                   ");
		sql.append("ON bid_contract.contract_id = bid_contract_party.contract_id   ");
		sql.append("LEFT JOIN contract_model                                       ");
		sql.append("ON bid_contract.contract_model_id = contract_model.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo                                         ");
		sql.append("ON bid_contract_party.contract_party = std_compinfo.comp_id    ");
		sql.append("WHERE bid_contract.contract_id = A.contract_id                 ");
		sql.append("AND bid_contract_party.is_party_a = 0 ) AS contractPartyB     ");
		sql.append("FROM bid_contract A                                            ");
		sql.append("LEFT JOIN bid_contract_party B ON A.contract_id = B.contract_id ");
		sql.append("LEFT JOIN contract_model C ON A.contract_model_id = C.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo D ON B.contract_party = D.comp_id       ");
		sql.append("LEFT JOIN bid_project E ON E.proj_id = A.proj_id               ");
		sql.append("WHERE 1 = 1                              ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("projId"))){
			sql.append(" AND A.proj_id = :projId");
			params.put("projId", page.getConditions().get("projId"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("status"))){
			sql.append(" AND A.`status` = :status");
			params.put("status", page.getConditions().get("status"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("contractTitle"))){
			sql.append(" AND A.contract_title like :contractTitle");
			params.put("contractTitle","%" + page.getConditions().get("contractTitle")+ "%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("auditStatus"))){
			sql.append(" AND A.audit_status = :auditStatus");
			params.put("auditStatus", Integer.parseInt(page.getConditions().get("auditStatus")));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compId"))){
			sql.append(" AND B.contract_party = :compId");
			params.put("compId", page.getConditions().get("compId"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("isPartyA"))){
			sql.append(" AND B.is_party_A = :isPartyA");
			params.put("isPartyA", Integer.parseInt(page.getConditions().get("isPartyA")));
		}
		sql.append(" order by A.upd_datetime DESC");
		return hibernateUtil.getSqlPageMapList2(sql.toString(), params, page);
	}
	

	/**
	 * @category 获取合同日志列表
	 * @author zhouqi
	 * @date 2015年9月9日15:10:35
	 * @param page
	 * @return Pagination
	 */
	@Override
	public Pagination getContractLogListPage(Pagination page){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                                        ");
		sql.append("A.contract_title AS contractTitle,                            ");
		sql.append("A.contract_id AS contractId,                                  ");
		sql.append("A.contract_code AS contractCode,                              ");
		sql.append("date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') AS updDatetime, ");
		sql.append("A.upd_user AS updUser,                                        ");
		sql.append("date_format(A.audit_datetime,'%Y-%m-%d %H:%i:%S') AS auditDatetime, ");
		sql.append("A.audit_status AS auditStatus,                                ");
		sql.append("A.`status` AS status,                                         ");
		sql.append("date_format(B.signup_datetime,'%Y-%m-%d %H:%i:%S')AS signupDatetime, ");
		sql.append("E.`name` AS projName,                                         ");
		sql.append("(SELECT std_compinfo.comp_name                                ");
		sql.append("FROM bid_contract                                             ");
		sql.append("LEFT JOIN bid_contract_party                                  ");
		sql.append("ON bid_contract.contract_id = bid_contract_party.contract_id  ");
		sql.append("LEFT JOIN contract_model                                      ");
		sql.append("ON bid_contract.contract_model_id = contract_model.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo                                        ");
		sql.append("ON bid_contract_party.contract_party = std_compinfo.comp_id   ");
		sql.append("WHERE bid_contract.contract_id = A.contract_id                ");
		sql.append("AND bid_contract_party.is_party_a = 1 ) AS contractPartyA,    ");
		sql.append("(SELECT std_compinfo.comp_name                                ");
		sql.append("FROM bid_contract                                             ");
		sql.append("LEFT JOIN bid_contract_party                                  ");
		sql.append("ON bid_contract.contract_id = bid_contract_party.contract_id  ");
		sql.append("LEFT JOIN contract_model                                      ");
		sql.append("ON bid_contract.contract_model_id = contract_model.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo                                         ");
		sql.append("ON bid_contract_party.contract_party = std_compinfo.comp_id    ");
		sql.append("WHERE bid_contract.contract_id = A.contract_id                 ");
		sql.append("AND bid_contract_party.is_party_a = 0 ) AS contractPartyB     ");
		sql.append("FROM bid_contract_log A                                        ");
		sql.append("LEFT JOIN bid_contract_party_log B ON A.contract_id = B.contract_id ");
		sql.append("LEFT JOIN contract_model C ON A.contract_model_id = C.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo D ON B.contract_party = D.comp_id       ");
		sql.append("LEFT JOIN bid_project E ON E.proj_id = A.proj_id               ");
		sql.append("WHERE 1=1 ");
		Map<String,Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("startPoint"))){
			sql.append("AND A.upd_datetime >= :startPoint");
			params.put("startPoint", DateUtil.StringTime2Timestamp(page.getConditions().get("startPoint")));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("endPoint"))){
			sql.append("AND A.upd_datetime < :endPoint");
			params.put("endPoint", DateUtil.StringTime2Timestamp(page.getConditions().get("endPoint")));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("contractId"))){
			sql.append("AND A.contract_id = :contractId");
			params.put("contractId", page.getConditions().get("contractId"));
		}
		sql.append(" order by A.upd_datetime DESC");
		return hibernateUtil.getSqlPageMapList2(sql.toString(), params, page);
	}
	
	/**
	 * @category 保存或更新合同
	 * @author zhouqi
	 * @param bidContract
	 * @return void
	 */
	public void saveOrUpdateContract(BidContract bidContract){
		Session session = sessionFactory.getCurrentSession();
		String contractId = bidContract.getContractId();
		LobHelper lobHelper = session.getLobHelper();
		try{
			Blob blob = lobHelper.createBlob(bidContract.getContent().getBytes("utf-8"));
			bidContract.setBlobContractContent(blob);
			session.saveOrUpdate(bidContract);
		}catch(Exception e){
			e.printStackTrace();
		}
		session.flush();
		session.clear();
	}
	
	/**
	 * @category 保存或更新合同日志
	 * @author zhouqi
	 * @param bidContractLog
	 * @return void
	 */
	public void saveOrUpdateContractLog(BidContractLog bidContractLog){
		Session session = sessionFactory.getCurrentSession();
		String logId = bidContractLog.getContractLogId();
		LobHelper lobHelper = session.getLobHelper();
		BidContractLog log = this.getBidContractLogById(logId);
		try{
			if(null==log){
				String content = bidContractLog.getContent();
				Blob blob = lobHelper.createBlob(content.getBytes("utf-8"));
				bidContractLog.setBlobContractContent(blob);
				session.save(bidContractLog);
			}else{
				session.update(bidContractLog);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		session.flush();
	}
	

	/**
	 * @category 根据合同id获取合同
	 * @author zhouqi
	 * @param contractId
	 * @return BidContract
	 */
	public BidContract getBidContractById(String contractId){
		Session session = sessionFactory.getCurrentSession();
		 BidContract bidContract = (BidContract) session.get(BidContract.class, contractId);
		 if(null == bidContract){
			 return null;
		 }
		 Blob blobContent =  bidContract.getBlobContractContent();
		 if(null==blobContent){
			 return bidContract;
		 }
		 try {
			String content = new String(blobContent.getBytes((long)1, (int)blobContent.length()),"utf-8");
			bidContract.setContent(content);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		 return bidContract;
	}
	
	/**
	 * @category 根据日志id获取合同日志
	 * @param logId
	 * @return
	 */
	public BidContractLog getBidContractLogById(String logId){
		 Session session = sessionFactory.getCurrentSession();
		 BidContractLog bidContractLog = (BidContractLog) session.get(BidContractLog.class, logId);
		 try {
			Blob blobContent =  bidContractLog.getBlobContractContent();
			String content = new String(blobContent.getBytes((long)1, (int)blobContent.length()),"utf-8");
			bidContractLog.setContent(content);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bidContractLog;
	}
	/**
	 * @category 保存合同与企业关联关系
	 * @author zhouqi
	 * @date 2015年9月20日14:38:28
	 * @param party
	 */
	@Override
	public void saveBidContractParty(BidContractParty party){
		Session session = sessionFactory.getCurrentSession();
		session.saveOrUpdate(party);
		session.flush();
		session.clear();
	}
	
	/**
	 * @category 保存合同与产品的关联关系
	 * @author zhouqi
	 * @date 2015年9月20日14:38:28
	 * @param party
	 */
	@Override
	public void saveBidContractProd(BidContractProd prod){
		Session session = sessionFactory.getCurrentSession();
		session.saveOrUpdate(prod);
		session.flush();
		session.clear();
	}
	
	/**
	 * @category 通过合同id获取合同甲方或者乙方
	 * @author zhouqi
	 * @date 2015年9月20日14:38:28
	 * @param party
	 */
	@Override
	public List<BidContractParty> getPartyAByContractId(String contractId, Integer isPartyA){
		Session session = sessionFactory.getCurrentSession();
	    StringBuffer sql = new StringBuffer();
	    sql.append("select * from bid_contract_party A where 1=1 ");
	    if(StringUtils.isNotBlank(contractId)){
	    	sql.append(" and A.contract_id=:contractId");
	    }
	    if(null!=isPartyA){
	    	sql.append(" and A.is_party_a =:isPartyA");
	    }
	    Query query = session.createSQLQuery(sql.toString()).addEntity(BidContractParty.class);
	    if(StringUtils.isNotBlank(contractId)){
	    	query.setParameter("contractId", contractId);
	    }
	    if(null!=isPartyA){
	    	query.setParameter("isPartyA", isPartyA);
	    }
	    return query.list(); 
	}
	
	/**
	 * @category 通过合同id获取产品
	 * @author zhouqi
	 * @date 2015年9月20日14:38:28
	 * @param party
	 */
	@Override
	public List<BidContractProd> getProdByContractId(String contractId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from bid_contract_prod A left join std_proddruginfo B ");
        sql.append("ON A.comp_prod_id = B.prod_id where A.`status`=1 AND B.`status`=1 ");
		if(StringUtils.isNotBlank(contractId)){
			sql.append(" and A.contract_id =:contractId");
		}
		Query query = session.createSQLQuery(sql.toString()).addEntity(BidContractProd.class);
		if(StringUtils.isNotBlank(contractId)){
			query.setParameter("contractId", contractId);
		}
		return query.list();
	}
	
	/**
	 * @category 保存合同日志文件日志
	 * @author zhouqi
	 * @date 2015年9月21日22:42:55
	 * @param bidContractLog
	 * @return void
	 */
	@Override
	public void saveContractLog(BidContractLog bidContractLog){
		Session session = sessionFactory.getCurrentSession();
		session.saveOrUpdate(bidContractLog);
		session.flush();
		session.clear();
	}
	
	/**
	 * @category 保存合同甲乙方日志
	 * @author zhouqi
	 * @date 2015年9月21日22:42:55
	 * @param bidContractParty
	 * @return void
	 */
	@Override
	public void saveConractPartyLog(BidContractPartyLog bidContractPartyLog){
		Session session = sessionFactory.getCurrentSession();
		session.saveOrUpdate(bidContractPartyLog);
		session.flush();
		session.clear();
	}
	
	/**
	 * @category 保存合同药品
	 * @author zhouqi
	 * @date 2015年9月21日22:42:55
	 * @param bidContactProd
	 * @return void
	 */
	@Override
	public void saveContractProdLog(BidContractProdLog bidContactProdLog){
		Session session = sessionFactory.getCurrentSession();
		session.saveOrUpdate(bidContactProdLog);
		session.flush();
		session.clear();
	}
	/**
	 * @category 获取修改前后对比
	 * @author zhouqi
	 * @date 2015年9月22日14:08:20
	 * @param updDatetime
	 * @return 
	 */
	@Override
	public List<Map<String, Object>>  getCompareLog(String contractId, String updDatetime){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT                                                         ");
		sql.append("A.contract_title AS contractTitle,                             ");
		sql.append("A.contract_id AS contractId,                                   ");
		sql.append("A.contract_content AS contractContent,                         ");
		sql.append("A.contract_code AS contractCode,                               ");
		sql.append("date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') AS updDatetime, ");
		sql.append("A.upd_user AS updUser,                                         ");
		sql.append("date_format(A.audit_datetime,'%Y-%m-%d %H:%i:%S') AS auditDatetime, ");
		sql.append("A.audit_status AS auditStatus,                                 ");
		sql.append("A.`status` AS status,                                          ");
		sql.append("date_format(B.signup_datetime,'%Y-%m-%d %H:%i:%S')AS signupDatetime, ");
		sql.append("E.`name` AS projName,                                          ");
		sql.append("(SELECT std_compinfo.comp_name                                 ");
		sql.append("FROM bid_contract                                              ");
		sql.append("LEFT JOIN bid_contract_party                                   ");
		sql.append("ON bid_contract.contract_id = bid_contract_party.contract_id   ");
		sql.append("LEFT JOIN contract_model                                       ");
		sql.append("ON bid_contract.contract_model_id = contract_model.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo                                         ");
		sql.append("ON bid_contract_party.contract_party = std_compinfo.comp_id    ");
		sql.append("WHERE bid_contract.contract_id = A.contract_id                 ");
		sql.append("AND bid_contract_party.is_party_a = 1 ) AS contractPartyA,     ");
		sql.append("D.comp_name AS contractPartyB                                  ");
		sql.append("FROM bid_contract_log A                                        ");
		sql.append("LEFT JOIN bid_contract_party_log B ON A.contract_id = B.contract_id ");
		sql.append("LEFT JOIN contract_model C ON A.contract_model_id = C.contract_model_id ");
		sql.append("LEFT JOIN std_compinfo D ON B.contract_party = D.comp_id       ");
		sql.append("LEFT JOIN bid_project E ON E.proj_id = A.proj_id               ");
		sql.append("WHERE 1 = 1 AND B.is_party_A = 0                               ");
		Map<String,Object> params = new HashMap<String,Object>();
		if(StringUtils.isNotBlank(updDatetime)){
			sql.append(" AND A.upd_datetime <:updDatetime");
			params.put("updDatetime", updDatetime);
		}
		if(StringUtils.isNotBlank(contractId)){
			sql.append(" AND A.contract_id =:contractId");
			params.put("contractId", contractId);
		}
		sql.append(" order by A.upd_datetime DESC limit 2");
		return (List<Map<String, Object>>) hibernateUtil.getSqlMapList(sql.toString(), params);	
	}
	
	/**
	 * @category 获取合同的前后对比 
	 * @author zhouqi
	 * @date 2015年9月22日14:08:20
	 * @param updDatetime
	 * @return 
	 */
	@Override
	public List<BidContractLog> getCompareContractLog(String contractId, String updDatetime){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM bid_contract_log A where 1=1");
		if(StringUtils.isNotBlank(contractId)){
			sql.append(" AND A.contract_id=:contractId");
		}
		if(StringUtils.isNotBlank(updDatetime)){
			sql.append(" AND A.upd_datetime < :updDatetime");
		}
	    Query query = session.createSQLQuery(sql.toString()).addEntity(BidContractLog.class);
	    if(StringUtils.isNotBlank(contractId)){
			query.setParameter("contractId", contractId);
		}
		if(StringUtils.isNotBlank(updDatetime)){
			query.setParameter("updDatetime", updDatetime);
		}
		sql.append(" order by A.upd_datetime DESC limit 2");
		return query.list();
	}
	
	/**
	 * @category 通过合同id获取药品信息
	 * @author zhouqi
	 * @date 2015年10月9日14:10:30
	 * @param contractId
	 * @return List<?>
	 */
	@Override
	public List<?> getDrugInfoByContractId(String contractId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT C.qualityspec_name as name, B.prod_id as drugId FROM bid_contract_prod A  JOIN std_proddruginfo B ON A.comp_prod_id = B.prod_id ");
		sql.append("JOIN std_drugqualityspec C ON C.qualityspec_id = B.qualityspec_id WHERE 1=1 and A.`status`=1 and B.`status`=1");
		if(StringUtils.isNotBlank(contractId)){
			sql.append(" AND A.contract_id = :contractId");
		}
		Query query = session.createSQLQuery(sql.toString());
		if(StringUtils.isNotBlank(contractId)){
			query.setParameter("contractId", contractId);
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		return list;
	}
	
	/**
	 * @category 通过合同id获取药品Id列表
	 * @author zhouqi
	 * @date 2015年10月9日14:10:30
	 * @param contractId
	 * @return List<?>
	 */
	@Override
	public List<String> getDrugIdsByContractId(String contractId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.comp_prod_id FROM bid_contract_prod A WHERE A.contract_id = :contractId");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("contractId",contractId);
		return query.list();
	}
	
	/**
	 * @category 企业库 信息查询及条件查询
	 * @author zhouqi
	 * @date 2015年10月12日18:49:13
	 * @param stdCompinfo
	 * @return
	 */
	@Override
	public Pagination queryStdCompinfoList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.comp_id compId,       ");
		sql.append("A.comp_code compCode,          ");
		sql.append("A.comp_name compName,          ");
		sql.append("A.org_code orgCode,            ");
		sql.append("A.comp_spel_code compSpelCode, ");
		sql.append("A.comp_type compType,          ");
		sql.append("A.area_id areaId,              ");
		sql.append("A.comp_reg_id compRegId,       ");
		sql.append("A.`status` STATUS,             ");
	    sql.append("A.upd_datetime updDatetime,    ");
		sql.append("B.ar_fullname arFullname,      ");
		sql.append("C.comp_reg_name compRegName    ");
	    sql.append("FROM std_compinfo A            ");
	    sql.append("LEFT JOIN dic_area B ON A.area_id = B.area_id "            );
	    sql.append("LEFT JOIN dic_comptype C ON C.comp_reg_id = A.comp_reg_id ");
	    sql.append("LEFT JOIN bid_projprodcompany D ON A.comp_id = D.prod_comp_id WHERE 1 = 1");
		Map<String, Object> param = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("projId"))){
			sql.append(" AND D.proj_id= :projId");
			param.put("projId", page.getConditions().get("projId"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compName"))){
			sql.append(" AND A.comp_name like :compName");
			page.getConditions().put("compName","%"+page.getConditions().get("compName")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compCode"))){
			sql.append(" AND A,comp_code = :compCode");
			page.getConditions().put("compCode",page.getConditions().get("compCode"));
		}
		return hibernateUtil.getSqlPageMapList(sql.toString(), param, page);
	}
}
